﻿CREATE TABLE [general_department] ([id_department] int IDENTITY NOT NULL, [department] nvarchar(50) NOT NULL, [department_address] ntext NOT NULL, [department_contact] nvarchar(50) NULL, [department_contact_phone] nvarchar(50) NULL)
CREATE TABLE [general_room] ([id_room] int IDENTITY NOT NULL, [id_department] int NOT NULL, [room] nvarchar(50) NOT NULL)
CREATE TABLE [equipment_type] ([id_type] int IDENTITY NOT NULL, [type] nvarchar(50) NOT NULL)
CREATE TABLE [equipment_mark] ([id_mark] int IDENTITY NOT NULL, [id_type] int NOT NULL, [mark] nvarchar(50) NOT NULL)
CREATE TABLE [equipment_model] ([id_model] int IDENTITY NOT NULL, [id_mark] int NOT NULL, [model] nvarchar(50) NOT NULL)
CREATE TABLE [equipments] ([id_equipment] int IDENTITY NOT NULL, [id_model] int NOT NULL, [id_room] int NOT NULL, [description] ntext NULL, [date_create] datetime NOT NULL, [inventory_number] nvarchar(50) NULL, [factory_number] nvarchar(50) NULL)
CREATE TABLE [equipments_move_history] ([id_move] int IDENTITY NOT NULL, [id_equipment] int NOT NULL, [move_date] datetime NOT NULL, [from_room] nvarchar(50) NOT NULL, [to_room] nvarchar(50) NOT NULL, [description] ntext NULL)
CREATE TABLE [endpoints_map] ([id] int IDENTITY NOT NULL, [dev_name] nvarchar(30) NULL, [dev_type] nvarchar(50) NOT NULL, [dev_ip] nvarchar(15) NULL, [pointA_X] float NOT NULL, [pointB_Y] float NOT NULL)
CREATE TABLE [endpoint_types] ([id_type] int NOT NULL, [type] nvarchar(50) DEFAULT ('') NOT NULL)
CREATE TABLE [endpoints] ([id_endpoint] int IDENTITY NOT NULL, [id_type] int NOT NULL, [id_room] int NOT NULL, [net_ip] nvarchar(15) NOT NULL, [phone] nvarchar(50) NULL, [endpoint] nvarchar(50) DEFAULT '' NOT NULL)
CREATE TABLE [contract_service] ([id_service] int IDENTITY NOT NULL, [service] nvarchar(50) NOT NULL)
CREATE TABLE [contract_provider] ([id_provider] int IDENTITY NOT NULL, [provider] nvarchar(50) NOT NULL, [provider_address] ntext NOT NULL, [default_contract_number] nvarchar(50) NOT NULL)
CREATE TABLE [contract_providerphone] ([id_providerphone] int IDENTITY NOT NULL, [id_provider] int NOT NULL, [providerphone] nvarchar(50) NOT NULL, [description] nvarchar(100) NOT NULL)
CREATE TABLE [contract_list] ([id_contract] int IDENTITY NOT NULL, [contract_number] nvarchar(50) NOT NULL, [id_service] int NOT NULL, [id_provider] int NOT NULL, [id_department] int NOT NULL, [contract_description] ntext NULL, [isclosed] bit NOT NULL)
ALTER TABLE [general_department] ADD CONSTRAINT [PK_contract_location] PRIMARY KEY ([id_department])
ALTER TABLE [general_room] ADD CONSTRAINT [PK_system_department] PRIMARY KEY ([id_room])
ALTER TABLE [equipment_type] ADD CONSTRAINT [PK_equipment_type] PRIMARY KEY ([id_type])
ALTER TABLE [equipment_mark] ADD CONSTRAINT [PK_equipment_mark] PRIMARY KEY ([id_mark])
ALTER TABLE [equipment_model] ADD CONSTRAINT [PK_equipment_model] PRIMARY KEY ([id_model])
ALTER TABLE [equipments] ADD CONSTRAINT [PK_equipments] PRIMARY KEY ([id_equipment])
ALTER TABLE [equipments_move_history] ADD CONSTRAINT [PK_equipments_move_history] PRIMARY KEY ([id_move])
ALTER TABLE [endpoint_types] ADD CONSTRAINT [PK_networks_type] PRIMARY KEY ([id_type])
ALTER TABLE [endpoints] ADD CONSTRAINT [PK_networks] PRIMARY KEY ([id_endpoint])
ALTER TABLE [contract_service] ADD CONSTRAINT [PK_contract_services] PRIMARY KEY ([id_service])
ALTER TABLE [contract_provider] ADD CONSTRAINT [PK_contract_provider] PRIMARY KEY ([id_provider])
ALTER TABLE [contract_providerphone] ADD CONSTRAINT [PK_contract_providerphone] PRIMARY KEY ([id_providerphone])
ALTER TABLE [contract_list] ADD CONSTRAINT [PK_contract_list] PRIMARY KEY ([id_contract])
ALTER TABLE [general_room] ADD CONSTRAINT [FK_general_room_general_department] FOREIGN KEY ([id_department]) REFERENCES [general_department]([id_department]) ON DELETE NO ACTION ON UPDATE NO ACTION
ALTER TABLE [equipment_mark] ADD CONSTRAINT [FK_equipment_mark_equipment_type] FOREIGN KEY ([id_type]) REFERENCES [equipment_type]([id_type]) ON DELETE NO ACTION ON UPDATE NO ACTION
ALTER TABLE [equipment_model] ADD CONSTRAINT [FK_equipment_model_equipment_mark] FOREIGN KEY ([id_mark]) REFERENCES [equipment_mark]([id_mark]) ON DELETE NO ACTION ON UPDATE NO ACTION
ALTER TABLE [equipments] ADD CONSTRAINT [FK_equipments_equipment_model] FOREIGN KEY ([id_model]) REFERENCES [equipment_model]([id_model]) ON DELETE NO ACTION ON UPDATE NO ACTION
ALTER TABLE [equipments] ADD CONSTRAINT [FK_equipments_general_room] FOREIGN KEY ([id_room]) REFERENCES [general_room]([id_room]) ON DELETE NO ACTION ON UPDATE NO ACTION
ALTER TABLE [equipments_move_history] ADD CONSTRAINT [FK_equipments_move_history_equipments] FOREIGN KEY ([id_equipment]) REFERENCES [equipments]([id_equipment]) ON DELETE NO ACTION ON UPDATE NO ACTION
ALTER TABLE [endpoints] ADD CONSTRAINT [FK_endpoints_endpoint_types] FOREIGN KEY ([id_type]) REFERENCES [endpoint_types]([id_type]) ON DELETE NO ACTION ON UPDATE NO ACTION
ALTER TABLE [endpoints] ADD CONSTRAINT [FK_networks_list_general_room] FOREIGN KEY ([id_room]) REFERENCES [general_room]([id_room]) ON DELETE NO ACTION ON UPDATE NO ACTION
ALTER TABLE [contract_providerphone] ADD CONSTRAINT [FK_contract_providerphone_contract_provider] FOREIGN KEY ([id_provider]) REFERENCES [contract_provider]([id_provider]) ON DELETE NO ACTION ON UPDATE NO ACTION
ALTER TABLE [contract_list] ADD CONSTRAINT [FK_contract_list_contract_provider] FOREIGN KEY ([id_provider]) REFERENCES [contract_provider]([id_provider]) ON DELETE NO ACTION ON UPDATE NO ACTION
ALTER TABLE [contract_list] ADD CONSTRAINT [FK_contract_list_contract_service] FOREIGN KEY ([id_service]) REFERENCES [contract_service]([id_service]) ON DELETE NO ACTION ON UPDATE NO ACTION
ALTER TABLE [contract_list] ADD CONSTRAINT [FK_contract_list_general_department] FOREIGN KEY ([id_department]) REFERENCES [general_department]([id_department]) ON DELETE NO ACTION ON UPDATE NO ACTION